from sqlalchemy import func

import db
from utils.util import ObjDictTool

session = db.db.session


def executeSql(sql, **kwargs):
    '''
    执行sql查询语句
    :param sql: sql语句
    :param kwargs: 参数
    :return:     sqlalchemy.engine.ResultProxy
    '''

    return session.execute(sql, **kwargs)


def save(Base):
    session.bulk_update_mappings
    session.add(Base)
    session.commit()

def saveOrNot(Obj, Base, *filters):
    try:
        data = session.query(Obj).filter(*filters).first()
        if data:
            session.commit()
        else:
            save(Base)
            session.commit()
    except:
        session.rollback()
        raise


def saveOrUpdate(Obj, Base, *filters):
    '''
    保存或者更新
    :param Obj: 类名
    :param Base: 对象
    :param filters: 条件
    :return:
    '''
    try:
        data = session.query(Obj).filter(*filters).first()
        if data:
            item = ObjDictTool.to_dic(Base)
            item['id'] = getattr(data, 'id')
            {setattr(data, k, v) for k, v in item.items()}
            session.commit()
        else:
            save(Base)
            session.commit()
    except:
        session.rollback()
        raise


def saveOrNONE(Obj, Base, *filters):
    try:
        data = session.query(Obj).filter(*filters).first()
        if not data:
            save(Base)
            session.commit()
    except:
        session.rollback()
        raise


def saveList(BaseList):
    if not BaseList:
        return
    # engine.execute(Base.__table__.insert(), BaseList)
    session.add_all(BaseList)
    session.commit()


def updateList(Base, BaseList):
    session.bulk_update_mappings(Base, BaseList)


def removeList(Base, BaseList):
    session.query(Base).filter(Base.id.in_(BaseList)).delete(synchronize_session=False)
    session.commit()

def removeByParam(Base, *filters):
    '''
    跟进条件删除
    :param Base: 类名
    :param filters:
    :return:
    '''
    session.query(Base).filter(*filters).delete(synchronize_session=False)
    session.commit()


def updateByParam(Obj, *params, **target):
    if not Obj:
        return
    '''

    :param Obj: 实体类
    :param params: 查询条件
    :param target: 更新目标值
    :return:
    '''
    session.query(Obj).filter(*params).update(target)
    session.commit()


def queryAll(Obj):
    return session.query(Obj).all()


def queryBySql(sql, **params):
    '''
    执行sql语句
    :param sql:
    :return:
    '''
    res = session.execute(sql, params)
    all_res_list = res.fetchall()
    return all_res_list


def queryByJoin(Obj1, Obj2, joinCondition, order_by, limit, *filters):
    return session.query(Obj1).join(Obj2, joinCondition).filter(*filters).order_by(order_by).limit(limit).all()


def queryJoin(Obj1, Obj2, joinCondition, *filters):
    return session.query(Obj1).outerjoin(Obj2, joinCondition).filter(*filters).all()


def queryOneByFilter(Obj, *filters):
    return session.query(Obj).filter(*filters).first()


def queryListByFilter(Obj, *filters):
    return session.query(Obj).filter(*filters).all()


def queryOder(Obj, *filters, orderSql):
    return session.query(Obj).filter(*filters).order_by(orderSql).all()


def queryByPage(Obj, *filters, orderSql, grouby, page_num, page_size):
    return session.query(Obj).filter(*filters).distinct(grouby).order_by(orderSql).limit(page_size).offset(
        (page_num - 1) * page_size).all()


def selectCount(Obj, *filters):
    return session.query(func.count(Obj)).filter(*filters).scalar()


def queryOderTOP(Obj, *filters, orderSql, top):
    return session.query(Obj).filter(*filters).order_by(orderSql).limit(top).all()


def fetch_to_dict(sql, params={}, fecth='all'):
    '''
    dict的方式返回数据
    :param sql: select * from xxx where name=:name
    :param params:{'name':'zhangsan'}
    :param fecth:默认返回全部数据，返回格式为[{},{}],如果fecth='one',返回单条数据，格式为dict
    :param bind:连接的数据，默认取配置的SQLALCHEMY_DATABASE_URL，
    :return:
    '''
    resultProxy = session.execute(sql, params)
    if fecth == 'one':
        result_tuple = resultProxy.fetchone()
        if result_tuple:
            result = dict(zip(resultProxy.keys(), list(result_tuple)))
        else:
            return None
    else:
        result_tuple_list = resultProxy.fetchall()
        if result_tuple_list:
            result = []
            keys = resultProxy.keys()
            for row in result_tuple_list:
                result_row = dict(zip(keys, row))
                result.append(result_row)
        else:
            return None
    return result


# 分页
def fetch_to_dict_pagetion(sql, params={}, page=1, page_size=15):
    sql_count = """select count(*) as count from (%s) _count""" % sql
    total_count = get_count(sql_count, params)
    sql_page = '%s limit %s,%s' % (sql, (page - 1) * page_size, page_size)
    print('sql_page:', sql_page)
    result = fetch_to_dict(sql_page, params, 'all')
    result_dict = {'results': result, 'count': total_count}
    return result_dict


# 执行单条语句（update,insert）
def execute(sql, params={}):
    print('sql', sql)
    session.execute(sql, params)
    session.commit()


def get_count(sql, params={}):
    return int(fetch_to_dict(sql, params, fecth='one').get('count'))


# 执行多条语句，失败自动回滚
def execute_many(sqls):
    print(sqls)
    if not isinstance(sqls, (list, tuple)):
        raise Exception('type of the parameters must be list or tuple')
    if len(sqls) == 0:
        raise Exception("parameters's length can't be 0")
    for statement in sqls:
        if not isinstance(statement, dict):
            raise Exception("parameters erro")
    try:
        for s in sqls:
            db.session.execute(s.get('sql'), s.get('params'))
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        raise Exception("execute sql fail ,is rollback")
